select * from loan_rbm where product = 'EXIMBIZBOOSTLOAN' and start_date < '2012-12-1'

SELECT 
	dbo.CalInterestWkly(10, 2012, 3000000,		22, '2012-9-13',	48, 62781.12) +
	dbo.CalInterestWkly(10, 2012, 3000000,		22, '2012-9-21',	48, 62781.12) +
	dbo.CalInterestWkly(10, 2012, 700000,		22, '2012-10-31',	30, 23399.68) +
	dbo.CalInterestWkly(10, 2012, 2000000,		22, '2012-11-15',	48, 41854.08) 
AS [Term:20]

select dbo.CalInterestMnthly(11, 2012, 3000000, 22, '2012-9-13', 48, 94518.23)* 2
select dbo.CalInterestMnthly(12, 2012, 3000000, 22, '2012-9-13', 48, 94518.23) * 2
select dbo.CalInterestMnthly(12, 2012, 2000000, 22, '2012-11-15', 48, 63012.16)

select dbo.CalculatePayment(3000000, 22, 48, 'MONTHLY')
select dbo.CalculatePayment(700000, 22, 30, 'MONTHLY')

exec dbo.GetAllInterestByMonthRBM 11, 2012





SELECT dbo.CalInterestMnthly(9, 2016, 3000000, 22, '2012-9-13', 48, 94518.23)

SELECT 
	dbo.CalInterestMnthly(1, 2012, 50000, 17.5, '2012-1-12', 5, 10441.72) AS [Jan 2012],
	dbo.CalInterestMnthly(2, 2012, 50000, 17.5, '2012-1-12', 5, 10441.72) AS [Feb 2012],
	dbo.CalInterestMnthly(3, 2012, 50000, 17.5, '2012-1-12', 5, 10441.72) AS [Mar 2012],
	dbo.CalInterestMnthly(4, 2012, 50000, 17.5, '2012-1-12', 5, 10441.72) AS [Apr 2012],
	dbo.CalInterestMnthly(5, 2012, 50000, 17.5, '2012-1-12', 5, 10441.72) AS [May 2012],
	dbo.CalInterestMnthly(6, 2012, 50000, 17.5, '2012-1-12', 5, 10441.72) AS [Jun 2012]
	

SELECT 
	dbo.CalInterestMnthly(1, 2012, 50000, 17.5, '2012-1-1', 5, 10441.72) AS [Jan 2012],
	dbo.CalInterestMnthly(2, 2012, 50000, 17.5, '2012-1-1', 5, 10441.72) AS [Feb 2012],
	dbo.CalInterestMnthly(3, 2012, 50000, 17.5, '2012-1-1', 5, 10441.72) AS [Mar 2012],
	dbo.CalInterestMnthly(4, 2012, 50000, 17.5, '2012-1-1', 5, 10441.72) AS [Apr 2012],
	dbo.CalInterestMnthly(5, 2012, 50000, 17.5, '2012-1-1', 5, 10441.72) AS [May 2012],
	dbo.CalInterestMnthly(6, 2012, 50000, 17.5, '2012-1-1', 5, 10441.72) AS [Jun 2012],
	dbo.CalInterestMnthly(7, 2012, 50000, 17.5, '2012-1-1', 5, 10441.72) AS [Jul 2012]
	
SELECT 
	dbo.CalInterestMnthly('2012-1-31', 50000, 17.5, '2012-1-12', 5, 10441.72) AS [Jan 2012],
	dbo.CalInterestMnthly('2012-2-29', 50000, 17.5, '2012-1-12', 5, 10441.72) AS [Feb 2012],
	dbo.CalInterestMnthly('2012-3-31', 50000, 17.5, '2012-1-12', 5, 10441.72) AS [Mar 2012],
	dbo.CalInterestMnthly('2012-4-30', 50000, 17.5, '2012-1-12', 5, 10441.72) AS [Apr 2012],
	dbo.CalInterestMnthly('2012-5-31', 50000, 17.5, '2012-1-12', 5, 10441.72) AS [May 2012],
	dbo.CalInterestMnthly('2012-6-30', 50000, 17.5, '2012-1-12', 5, 10441.72) AS [Jun 2012]
	
SELECT 
	dbo.CalInterestMnthly('2012-6-15', 50000, 17.5, '2012-2-28', 5, 10441.72) AS [Jan 2012]
	
SELECT dbo.CalInterestWkly('2012-2-20', 10000, 17.5, '2012-1-23', 8, 2521.07)
	
SELECT DATEDIFF(day, '2012-1-1', '2012-1-2')


exec RedBalTest

--LTER FUNCTION [dbo].[CalInterestWkly]
--(
--	@report_date DATETIME,
--	@principal DECIMAL(18, 2),
--	@interest_rate DECIMAL(18, 8),
--	@loan_start_date DATETIME,	
--	@term INTEGER,	
--	@payment_amount DECIMAL(18, 2)	
--)
--RETURNS DECIMAL(18, 2)
--AS
--BEGIN

--	DECLARE @interest DECIMAL(18, 8) = 0,
--		@wkly_interest DECIMAL(18, 8) = 0,	
--		@payment_date DATETIME = DATEADD(wk, 1, @loan_start_date),
--		@loan_end_date DATETIME = DATEADD(week, @term, @loan_start_date),
--		@num_days INTEGER,	
--		@month_end DATETIME = @loan_start_date, 
--		@counter INTEGER = 1
	
--	SET @interest_rate = (@interest_rate / 100) / 52
	
--	IF @loan_start_date < @report_date AND (@loan_end_date > @report_date OR (YEAR(@loan_end_date) = YEAR(@report_date) AND MONTH(@loan_end_date) = MONTH(@report_date)))
--	BEGIN
--		WHILE @month_end < @report_date
--		BEGIN
--			/*Check if the report is for the first month of the loan*/
--			IF YEAR(@loan_start_date) = YEAR(@report_date) AND MONTH(@loan_start_date) = MONTH(@report_date)
--			BEGIN
--				IF @report_date > @loan_end_date --ensure that the loan is not calculated pass the expiry date
--				BEGIN
--					SET @month_end = @loan_end_date
--				END
--				ELSE
--				BEGIN
--					SET @month_end = @report_date
--				END
--			END
--			ELSE --if the report is not for the first month of the loan
--			BEGIN
--				IF DATEADD(month, 1, @month_end) > @loan_end_date OR DATEADD(month, 1, @month_end) > @report_date--ensure that the loan is not calculated pass the expiry date
--				BEGIN
--					IF DATEADD(month, 1, @month_end) > @loan_end_date
--					BEGIN
--						SET @month_end = @loan_end_date
--					END
--					ELSE
--					BEGIN
--						SET @month_end = @report_date
--					END
--				END
--				ELSE
--				BEGIN
--					SET @month_end = DATEADD(day, DAY(DATEADD(mm, DATEDIFF(mm, -1, @month_end), -1)) - DAY(@month_end), @month_end)
--				END
--				--SET @month_end = DATEADD(day, DAY(DATEADD(mm, DATEDIFF(mm, -1, @loan_start_date), -1)) - DAY(@loan_start_date), @loan_start_date)
--			END
			
--			WHILE @payment_date <= @month_end
--			BEGIN
--				SET @wkly_interest = @principal * @interest_rate
--				SET @interest = @interest + @wkly_interest
--				SET @principal = @principal + @wkly_interest - @payment_amount
				
--				IF DATEADD(week, 1, @payment_date) > @month_end
--				BEGIN
--					SET @num_days = DATEDIFF(day, @payment_date, @month_end)
--					SET @wkly_interest = @principal * ((@interest_rate * 52) / 365) * @num_days
--					SET @interest = @interest + @wkly_interest
--					SET @principal = @principal + @wkly_interest - @payment_amount
--				END
				
--				SET @payment_date = DATEADD(week, 1, @payment_date)
--			END
			
--		END
		
--		--IF YEAR(@loan_start_date) = YEAR(@report_date) AND MONTH(@loan_start_date) = MONTH(@report_date)
--		--BEGIN
--		--	SET @num_days = DATEDIFF(day, @loan_start_date, @report_date)			
--		--END
--	--	ELSE
--	--	BEGIN
--	--		SET @num_days = DATEDIFF(day, @loan_start_date, @month_end)	
--	--	END		
		 
				
--	--	SET @interest = @principal * @daily_interest_rate * @num_days
--	--	SET @principal = @principal + @interest - @payment_amount 
--	--	SET @month_end = DATEADD(month, 1, @month_end)
		
		
--	--	WHILE (YEAR(@month_end) < YEAR(@report_date) OR (YEAR(@month_end) = YEAR(@report_date) AND MONTH(@month_end) < MONTH(@report_date)))
--	--	BEGIN
--	--		SET @interest = @principal * @mnthly_interest_rate			
--	--		SET @principal = @principal + @interest - @payment_amount
--	--		SET @month_end = DATEADD(month, 1, @month_end)
--	--	END	
		
--	--	IF YEAR(@month_end) = YEAR(@report_date) AND MONTH(@month_end) = MONTH(@report_date)
--	--	BEGIN
--	--		IF @report_date <= @loan_end_date
--	--		BEGIN
--	--			SET @num_days = DAY(@report_date)
--	--		END
--	--		ELSE
--	--		BEGIN
--	--			SET @num_days = DAY(@loan_end_date)
--	--		END
			
--	--		SET @interest = @principal * @daily_interest_rate * @num_days
--	--	END	
--	--END
--	--RETURN @interest
	
	

--	--SET @interest_rate = (@interest_rate / 100) / 52

--	--IF @payment_date < @report_date
--	--BEGIN
--	--	WHILE (YEAR(@payment_date) < YEAR(@report_date) OR (YEAR(@payment_date) = YEAR(@report_date) AND MONTH(@payment_date) < MONTH(@report_date)))
--	--	BEGIN
--	--		SET @wkly_interest = @principal * @interest_rate
--	--		SET @interest = @interest + @wkly_interest
--	--		SET @principal = @principal + @wkly_interest - @payment_amount
--	--		SET @payment_date = DATEADD(wk, 1, @payment_date)
--	--		SET @counter = @counter + 1
--	--	END	

--	--	SET @month_end = DATEADD(day, DAY(DATEADD(mm, DATEDIFF(mm, -1, @payment_date), -1)) - DAY(@payment_date), @payment_date)
--	--	SET @interest = 0

--	--	WHILE @payment_date <= @report_date
--	--	BEGIN
--	--		IF @counter <= @term
--	--		BEGIN
--	--			SET @wkly_interest = @principal * @interest_rate
--	--			SET @interest = @interest + @wkly_interest
--	--			SET @principal = @principal + @wkly_interest - @payment_amount
				
--	--			SET @payment_date = DATEADD(wk, 1, @payment_date)
--	--			IF @payment_date > @report_date
--	--			BEGIN
--	--				SET @wkly_interest = ((@interest_rate * 52) / 365 ) * @principal * DATEDIFF(day, @report_date, @payment_date)
--	--				SET @interest = @interest + @wkly_interest
--	--			END
--	--			SET @counter = @counter + 1
--	--		END	
--	--		ELSE
--	--		BEGIN
--	--			BREAK
--	--		END	
--	--	END
--	END
--	RETURN @interest
--END;

	

